﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class OptionDAL : IOptionDAL
    {
        private IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();

        public OptionDAL()
        {

        }

        public IList<TreeViewInfo> TreeView(int type)
        {
            string strSQL;
            IList<TreeViewInfo> treeView = new List<TreeViewInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@type", type);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT S.BGM_OPTION_SEQ AS ID, S.BGM_OPTION_NAME AS NAME"
                         + ", S.UPPER_BGM_OPTION_SEQ AS PARENT"
                         + ", S.BGM_OPTION_NAME AS TEXT_FIELD, S.BGM_OPTION_SEQ AS VALUE_FIELD"
                         + " FROM BGM_MAS_OPTION S"
                         + " WHERE S.RECORD_STATUS not like @status"
                         + " AND BGM_OPTION_TYPE_SEQ = @type"
                         + " ORDER BY S.BGM_OPTION_CODE";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    TreeViewInfo opt = new TreeViewInfo();

                    opt.ID = Convert.ToString(dr["BGM_OPTION_SEQ"]);
                    opt.Name = Convert.ToString(dr["BGM_OPTION_NAME"]);
                    opt.Parent = Convert.ToString(dr["UPPER_BGM_OPTION_SEQ"]);
                    opt.TextField = Convert.ToString(dr["BGM_OPTION_NAME"]);
                    opt.ValueField = Convert.ToString(dr["BGM_OPTION_SEQ"]);

                    treeView.Add(opt);
                }

                //while (rdr.Read())
                //{
                //    TreeViewInfo opt = new TreeViewInfo(rdr.GetString(0), rdr.GetString(1)
                //                                        , (rdr.IsDBNull(2)) ? null : rdr.GetString(2), rdr.GetString(3),
                //                                        rdr.GetString(4));
                //    treeView.Add(opt);
                //}

                return treeView;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Insert(OptionInfo optInfo)
        {
            string strSQL;
            Nullable<int> parent = null;

            if (optInfo.Parent.Sequence > 0)
                parent = optInfo.Parent.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@code", optInfo.Code);
                dbManager.AddParameters(1, "@name", optInfo.Name);
                dbManager.AddParameters(2, "@descr", optInfo.Description);
                dbManager.AddParameters(3, "@optionParent", parent);
                dbManager.AddParameters(4, "@optionType", optInfo.Type);
                dbManager.AddParameters(5, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@user", optInfo.User.CreatedBy);
                dbManager.AddParameters(7, "@bgm_year", optInfo.Year);

                strSQL = "INSERT INTO BGM_MAS_OPTION (BGM_OPTION_CODE, BGM_OPTION_NAME, BGM_OPTION_DESCR"
                         + ", UPPER_BGM_OPTION_SEQ, BGM_OPTION_TYPE_SEQ, RECORD_STATUS, CREATED_BY, BGM_YEAR)"
                         + " VALUES (@code, @name, @descr, @optionParent, @optionType, @status, @user, @bgm_year) ";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(OptionInfo optInfo)
        {
            string strSQL;
            Nullable<int> parent = null;

            if (optInfo.Parent.Sequence > 0)
                parent = optInfo.Parent.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@seq", optInfo.Sequence);
                dbManager.AddParameters(1, "@code", optInfo.Code);
                dbManager.AddParameters(2, "@name", optInfo.Name);
                dbManager.AddParameters(3, "@descr", optInfo.Description);
                dbManager.AddParameters(4, "@optionParent", parent);
                dbManager.AddParameters(5, "@optionType", optInfo.Type);
                dbManager.AddParameters(6, "@status", SystemInfo._ActiveRecordStatus);

                strSQL = "UPDATE BGM_MAS_OPTION"
                         + " SET BGM_OPTION_CODE = @code"
                         + ", BGM_OPTION_NAME = @name"
                         + ", BGM_OPTION_DESCR = @descr"
                         + ", UPPER_BGM_OPTION_SEQ = @optionParent"
                         + ", BGM_OPTION_TYPE_SEQ = @optionType"
                         + ", RECORD_STATUS = @status"
                         + " WHERE BGM_OPTION_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text,
                                                 "UPDATE BGM_MAS_OPTION SET RECORD_STATUS = @status WHERE BGM_OPTION_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<OptionInfo> Search(OptionInfo optInfo)
        {
            string strSQL;
            //int parent = 0;
            //int type = 0;

            //if (optInfo.Parent != null)
            //    parent = optInfo.Parent.Sequence;

            //if (optInfo.Type != null)
            //    type = optInfo.Type.Sequence;

            List<OptionInfo> srcList = new List<OptionInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@seq", optInfo.Sequence);
                dbManager.AddParameters(1, "@code", optInfo.Code);
                dbManager.AddParameters(2, "@name", optInfo.Name);
                dbManager.AddParameters(3, "@descr", optInfo.Description);
                dbManager.AddParameters(4, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(5, "@parent", optInfo.Parent.Sequence);
                dbManager.AddParameters(6, "@type", optInfo.Type);
                dbManager.AddParameters(7, "@bgm_year", optInfo.Year);


                strSQL = "SELECT S.BGM_OPTION_SEQ, S.BGM_OPTION_CODE, S.BGM_OPTION_NAME, S.BGM_OPTION_DESCR"
                         + ", S.RECORD_STATUS, S.UPPER_BGM_OPTION_SEQ, S.BGM_OPTION_TYPE_SEQ, S.BGM_YEAR"
                         + ", T.BGM_OPTION_TYPE_NAME, T.HAS_CHILD"
                         + " FROM BGM_MAS_OPTION S"
                         + " INNER JOIN BGM_SYSTEM_OPTION_TYPE T ON S.BGM_OPTION_TYPE_SEQ = T.BGM_OPTION_TYPE_SEQ"
                         + " WHERE S.RECORD_STATUS not like @status"
                         + ((optInfo.Sequence > 0) ? " AND S.BGM_OPTION_SEQ = @seq" : "")
                         + ((optInfo.Code.Length > 0) ? " AND S.BGM_OPTION_CODE = @code" : "")
                         + ((optInfo.Name.Length > 0) ? " AND S.BGM_OPTION_NAME = @name" : "")
                         + ((optInfo.Description.Length > 0) ? " AND S.BGM_OPTION_DESCR = @descr" : "")
                         + ((optInfo.Parent.Sequence > 0) ? " AND S.UPPER_BGM_OPTION_SEQ = @parent" : "")
                         + ((optInfo.Type > 0) ? " AND S.BGM_OPTION_TYPE_SEQ = @type" : "")
                         + ((optInfo.Year != null) ? " AND S.BGM_YEAR = @bgm_year" : "")
                         + " ORDER BY S.BGM_OPTION_CODE";


                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    OptionInfo optParentInfo = new OptionInfo();

                    if (dr["UPPER_BGM_OPTION_SEQ"] == DBNull.Value)
                        optParentInfo = null;
                    else optParentInfo.Sequence = Convert.ToInt32(dr["UPPER_BGM_OPTION_SEQ"]);

                    OptionInfo src = new OptionInfo();

                    src.Sequence = Convert.ToInt32(dr["BGM_OPTION_SEQ"]);
                    src.Code = (dr["BGM_OPTION_CODE"]).ToString();
                    src.Name = (dr["BGM_OPTION_NAME"]).ToString();
                    src.Description = (dr["BGM_OPTION_DESCR"]).ToString();
                    src.Status = Convert.ToString(dr["RECORD_STATUS"]);
                    src.Type = Convert.ToInt32(dr["BGM_OPTION_TYPE_SEQ"]);
                    src.Year = cn.ConvertNullInt(dr["BGM_YEAR"]);
                    src.Parent = optParentInfo;

                    srcList.Add(src);
                }
                //while (rdr.Read()),ii
                //{
                //    OptionInfo optParentInfo = new OptionInfo();

                //    if (rdr.IsDBNull(5))
                //        optParentInfo = null;
                //    else optParentInfo.Sequence = rdr.GetInt32(5);

                //    OptionInfo src = new OptionInfo(rdr.GetInt32(0), (rdr.IsDBNull(1)) ? "" : rdr.GetString(1)
                //                                    , (rdr.IsDBNull(2)) ? "" : rdr.GetString(2)
                //                                    , (rdr.IsDBNull(3)) ? "" : rdr.GetString(3)
                //                                    , rdr.GetString(4), rdr.GetInt32(6),
                //                                    (rdr.IsDBNull(7) ? -1 : rdr.GetInt32(7))
                //                                    , optParentInfo, null);

                //    srcList.Add(src);
                //}

                return srcList;
            }
            catch (Exception e)
            {
                return null;
                throw e;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public bool CheckDuplicateCode(string code, string sequence)
        {
            string strSQL;
            bool result = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@deletestatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_option_code", code);
                dbManager.AddParameters(2, "@bgm_option_seq", sequence);

                strSQL = @"SELECT bgm_option_code
                           FROM bgm_mas_option
                           WHERE bgm_option_code = @bgm_option_code
                           AND record_status not like @deletestatus " +
                         (string.IsNullOrEmpty(sequence)
                              ? string.Empty
                              : " AND bgm_option_seq not like @bgm_option_seq");



                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
               
                foreach (DataRow dr in dtb.Rows)
                {
                    result = true;
                    break;
                }
                //while (rdr.Read())
                //{
                //    result = true;
                //    break;
                //}
            }
            catch (Exception Error)
            {
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return result;
        }

    }
}